package game.round.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.Statement;

import game.round.dao.IARoundDao;
import game.round.pojo.ARound;
import game.util.DBUtil;

public class ARoundDaoImpl implements IARoundDao
{
	/*添加一条信息*/
	public int insertDao(ARound round)
	{
		String sql = "INSERT INTO `game`.`a_round`"
				+ "(`usersId`, "
				+ "`name`, `content`, "
				+ "`totalCount`, `winCount`, "
				+ "`loseCount`, `perScore`, "
				+ "`status`, `createTime`, "
				+ "`updateTime`, `pubTime`) VALUES"
				+ " (?,?,?,?,?,?,?,?,?,?,?)";
		DBUtil db = new DBUtil();
		Connection con = db.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try
		{
			ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			/*为占位符赋值*/
			ps.setObject(1, round.getUsersId());
			ps.setObject(2, round.getName());
			ps.setObject(3, round.getContent());
			ps.setObject(4, round.getTotalCount());
			ps.setObject(5, round.getWinCount());
			ps.setObject(6, round.getLoseCount());
			ps.setObject(7, round.getPerScore());
			ps.setObject(8, round.getStatus());
			ps.setObject(9, round.getCreateTime());
			ps.setObject(10, round.getUpdateTime());
			ps.setObject(11, round.getPubTime());
			int sre = ps.executeUpdate();
			/*获取下一个主键*/
			rs = ps.getGeneratedKeys();
			if(rs.next())
			{
				int id = rs.getInt(1);
				round.setId(id);
			}
			return sre ;
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			db.closeAll(con, ps, rs);
		}
		
		return 0;
	}
	/*更新一条信息*/
	public int updateDao(ARound round)
	{
		/*创建一个sql语句*/
		String sql = "update `game`.`a_round` set `usersId` = ?,"
				+ " `name` = ?, `content` = ?,"
				+ " `totalCount` = ?, `winCount` = ?,"
				+ " `loseCount` = ?, `perScore` = ?, "
				+ "`status` = ?, `createTime` = ?, `updateTime` = ?,"
				+ " `pubTime` = ? where `id` =?";
		DBUtil db =new DBUtil();
		Connection con = db.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try
		{
			ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			/*为占位符赋值*/
			ps.setObject(1, round.getUsersId());
			ps.setObject(2, round.getName());
			ps.setObject(3, round.getContent());
			ps.setObject(4, round.getTotalCount());
			ps.setObject(5, round.getWinCount());
			ps.setObject(6, round.getLoseCount());
			ps.setObject(7, round.getPerScore());
			ps.setObject(8, round.getStatus());
			ps.setObject(9, round.getCreateTime());
			ps.setObject(10, round.getUpdateTime());
			ps.setObject(11, round.getPubTime());
			ps.setObject(12, round.getId());
			int res = ps.executeUpdate();
			/*获取结果集*/
			rs = ps.getGeneratedKeys();
			if(rs.next())
			{
				int id = rs.getInt(1);
				round.setId(id);
			}
			return res;
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			db.closeAll(con, ps, rs);
		}
		return 0;
	}
	/*删除一条信息*/
	public int deleteDao(ARound round)
	{
		return 0;
	}
	/*查询一条信息*/
	public ARound findOneDao(Map<String, Object> conMap)
	{
		String sql = "select * from a_round where id = ? " ;
		DBUtil db= new DBUtil();
		Connection con = db.getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		try
		{
			ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			//为占位符赋值
			ps.setObject(1, conMap.get("id"));
			//获取结果集
			rs = ps.executeQuery();
			
			if(rs.next())
			{
				ARound round = convertFromDb(rs);
				return round;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			db.closeAll(con, ps, rs);
		}
		
		return null;
	}
	/*public ARound findOneDao(Map<String, Object> condMap)
	{
		String sql = "select * from a_round where id = ? " ; 
		//String sql = "select * from a_round where id = ? " ;
		DBUtil dbUtil = new DBUtil() ; 
		 获取一个链接 
		Connection connection = dbUtil.getConnection() ; 
		PreparedStatement pst = null ;
		ResultSet rs = null ; 
		try
		{
			pst = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			pst.setObject(1, condMap.get("id"));
			rs = pst.executeQuery() ;
			if(rs.next())
			{
				ARound round = convertFromDb(rs);
				return round ; 
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			dbUtil.closeAll(connection, pst, rs);
		}
		return null;
	}*/

	/*查询所有信息*/
	public List<ARound> findCondListDao(Map<String, Object> conMap)
	{
		/*要存储搜索的条件 就要储存的List中*/
		List<Object> paramsList = new ArrayList<Object>();
		List<ARound> resultList = new ArrayList<ARound>();
		String sql = "select * from a_round ";
		if(conMap.get("usersId") !=null && !"".equalsIgnoreCase(conMap.get(conMap) + ""))
		{
			sql = sql +"where usersId = ? ";
			/* 主要存储搜索的条件 */
			paramsList.add(conMap.get("usersId"));
		}
		DBUtil db = new DBUtil();
		/*开始连接*/
		Connection con = db.getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		try
		{
			ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			int count = 1;
			for (Iterator iterator = paramsList.iterator(); iterator.hasNext();)
			{
				Object val = (Object) iterator.next();
				ps.setObject(count, val);
				count ++ ;
			}
			/*获取下一个主键*/
			rs = ps.executeQuery();
			while(rs.next())
			{
				ARound round = convertFromDb(rs);
				/*将数据表中的内容放到List中*/
				resultList.add(round);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			db.closeAll(con, ps, rs);
		}
		return resultList;
	}
	private ARound convertFromDb(ResultSet rs) throws SQLException
	{
		/*将数据库中的每一条记录
		 * 与Java中的对象 一一对应起来
		 * */
		int id = rs.getInt("id");
		int usersId = rs.getInt("usersId");
		String name = rs.getString("name");
		String content = rs.getString("content");
		int totalCount = rs.getInt("totalCount");
		int winCount = rs.getInt("winCount");
		int loseCount = rs.getInt("loseCount");
		int perScore = rs.getInt("perScore");
		byte status = rs.getByte("status");
		Date createTime = rs.getTimestamp("createTime");
		Date updateTime = rs.getTimestamp("updateTime");
		Date pubTime = rs.getTimestamp("pubTime");
		
		ARound round = new ARound() ; 
		
		round.setId(id);
		round.setUsersId(usersId);
		round.setName(name);
		round.setContent(content);
		round.setTotalCount(totalCount);
		round.setWinCount(winCount);
		round.setLoseCount(loseCount);
		round.setPerScore(perScore);
		round.setStatus(status);
		round.setCreateTime(createTime);
		round.setUpdateTime(updateTime);
		round.setPubTime(pubTime);
		return round;
	}

}
